package mysql;

import pojo.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author DASK
 * @date 2020/4/1 23:03
 * @description //TODO 数据库工具
 */
public class DBTool {
    public static List<Book> listBook() {
        return queryBook("SELECT * FROM tb_books");
    }

    public static Book getBookById(int bookId) {
        String sql = String.format("SELECT * FROM tb_books WHERE id=%s", bookId);
        List<Book> books = queryBook(sql);
        return books.size() > 0 ? books.get(0) : null;
    }

    public static List<Book> searchBookByName(String keyword) {
        return queryBook("SELECT * FROM tb_books WHERE book_Name LIKE '%" + keyword + "%'");
    }

    //查询所有书信息
    public static List<Book> queryBook(String sql) {
        List<Book> books = new ArrayList<>();

        try {
            Connection connection = MySQLPool.getAvailableConnection();
            if (connection == null) {
                throw new NullPointerException("无法获取数据库连接");
            }
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("book_Name");
                float price = rs.getFloat("book_Price");
                String author = rs.getString("book_Author");
                books.add(new Book(id, name, price, author));
            }

            stmt.close();
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    //添加一本书
    public static int addBook(String name, float price, String author) {
        String sql = String.format("INSERT INTO tb_books(book_Name, book_Price,book_Author) values('%s', %f,'%s')", name, price, author);
        System.out.println(sql);
        return updateBook(sql);
    }

    public static int addBook(Book book) {
        String sql = String.format("INSERT INTO tb_books(book_Name, book_Price,book_Author) values('%s', %f,'%s')", book.getName(), book.getPrice(), book.getAuthor());
        System.out.println(sql);
        return updateBook(sql);
    }

    //删除一本书籍
    public static int delBook(int bookId) {
        String sql = String.format("DELETE FROM tb_books where id=%s", bookId);
        System.out.println(sql);
        return updateBook(sql);
    }

    //修改一本书的内容
    public static int updateBook(Book book) {
        String sql = String.format("UPDATE tb_books SET book_Name='%s', book_Price=%f, book_Author='%s' WHERE id=%s",
                book.getName(), book.getPrice(), book.getAuthor(), book.getId()
        );
        System.out.println(sql);
        return updateBook(sql);
    }

    public static int updateBook(String sql) {
        try {
            Connection connection = MySQLPool.getAvailableConnection();
            if (connection == null) {
                throw new NullPointerException("无法获取数据库连接");
            }
            PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            ResultSet set = stmt.getGeneratedKeys();
            if (set.next()) {
                return set.getInt(1);
            }
            stmt.close();
            return -1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //通过作者查询其下书名
    public static List<Book> searchBooksByAuthor(String author) {
        String sql = String.format("SELECT * FROM tb_books where book_Author LIKE '%%%s%%'", author);
        List<Book> list = new ArrayList<>();
        try {
            Connection connection = MySQLPool.getAvailableConnection();
            if (connection == null) {
                throw new NullPointerException("无法获取数据库连接");
            }
            Statement stmt = connection.createStatement();
            ResultSet set = stmt.executeQuery(sql);

            while (set.next()) {
                int id = set.getInt("id");
                String bookName = set.getString("book_Name");
                double bookPrice = set.getFloat("book_Price");
                String bookAuthor = set.getString("book_Author");
                list.add(new Book(id, bookName, bookPrice, bookAuthor));
            }
            stmt.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


}
